package org.elcom.source;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.BatchUpdateException;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.NumberFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Scanner;

public class InsertIntoTable {

    public static void executeInsert(String filePath, PreparedStatement stmt) throws IOException {
        int indexOfLine = 0;
        int count = 0;

//    	File file = new File(filePath);
//    	FileReader fr = new FileReader(file);
//    	BufferedReader br = new BufferedReader(fr);
//    	while(br.readLine() != null){

        FileInputStream file = new FileInputStream(filePath);
        Scanner input = new Scanner(file);

        while (input.hasNextLine()) {

            String str = input.nextLine();
//    		String str = br.readLine();
            if (str != null) {
                if (str.contains("|")) {  //tach String thanh nhieu phan ngan cach boi "|"
                    String[] parts = null;
                    parts = str.split("\\|", -1);

                    //check Empty field
                    if (parts[0].isEmpty() || parts[11].isEmpty()) {
//                    	System.out.println("aaa");
                        continue;
                    }
                    //check Valid Date
                    if (!isValidDate(parts[1])) {
//                    	System.out.println(parts[1]);
                        continue;
                    }
                    //check Valid Number
                    if (!isNumberic(parts[8])) {
                        continue;
                    }
                    if (!isNumberic(parts[9])) {
                        continue;
                    }
                    if (!isNumberic(parts[10])) {
                        continue;
                    }
                    if (!isInteger(parts[11])) {
                        continue;
                    }
                    // gan cac truong rong = "NULL"
                    for (int i = 0; i < parts.length; i++) {
                        if (parts[i].isEmpty()) {
                            parts[i] = "NULL";
                        }
                    }
                    indexOfLine++;
                    count++;

                    try {
                        VASItem vasItem = new VASItem(parts);
                        stmt.setString(1, vasItem.getMSISDN());
                        stmt.setString(2, vasItem.getTime());
                        stmt.setString(3, vasItem.getProgram_ID());
                        stmt.setString(4, vasItem.getContent());
                        stmt.setString(5, vasItem.getLac());
                        stmt.setString(6, vasItem.getCell_ID());
                        stmt.setString(7, vasItem.getShort_Code());
                        stmt.setString(8, vasItem.getTitle());
                        try {
                            stmt.setDouble(9, Double.parseDouble((vasItem.getMenu_Index())));
                        } catch (Exception ex) {
                            stmt.setNull(9, java.sql.Types.DOUBLE);
                        }
                        try {
                            stmt.setInt(10, Integer.parseInt(vasItem.getLongitude()));
                        } catch (Exception ex) {
                            stmt.setNull(10, java.sql.Types.DOUBLE);
                        }
                        try {
                            stmt.setInt(11, Integer.parseInt(vasItem.getLatitude()));
                        } catch (Exception ex) {
                            stmt.setNull(11, java.sql.Types.DOUBLE);
                        }
                        try {
                            stmt.setInt(12, Integer.parseInt(vasItem.getVAS_Trans_ID()));
                        } catch (Exception ex) {
                            stmt.setNull(12, java.sql.Types.INTEGER);
                        }

                        stmt.addBatch();

                        if (count > Config.batchSize - 1) {

                            int[] result = null;
//                			System.out.println("i = " + i);
                            try {
                                ExecutionTimer t = new ExecutionTimer();
                                t.start();
                                result = stmt.executeBatch();

                                for (int j = 0; j < result.length; j++) {
                                    if (result[j] == PreparedStatement.EXECUTE_FAILED) {
                                        System.out.println("Error at line " + j);
                                        retryInsertFromError(filePath, stmt, j);
                                    }
                                }

                                t.end();
                                System.out.println("Execute Batch: " + t.duration() + " ms");

                                Connect.connect.commit();
                                System.out.println("Count = " + indexOfLine);
                                System.out.println("Commit successful");
                                stmt.clearBatch();
                                count = 0;
                            } catch (BatchUpdateException e) {
                                System.out.println("1");
//                				moveToErrorFolder(list.get(i), String.valueOf(i));
                            } catch (SQLException e) {
                                System.out.println("2");
                            } catch (Exception e) {
                            }

//                			stmt.close();
                        }

                    } catch (Exception e) {
                        System.out.println("error");
                    }

                }
//            	count++;
            }
        }
//		System.out.println("cou " + count);
//		System.out.println("ind " + indexOfLine);
        try {
            stmt.executeBatch();
            Connect.connect.commit();
            System.out.println("Count = " + indexOfLine);
            System.out.println("Commit successful");
            stmt.clearBatch();
        } catch (BatchUpdateException e) {

            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        System.gc();
        file.close();
        input.close();
//        fr.close();
//        br.close();
    }

    public static void retryInsertFromError(String filePath, PreparedStatement stmt, int line) {
        int count = 0;
        try {
            File file = new File(filePath);
            FileReader fr = new FileReader(file);
            BufferedReader br = new BufferedReader(fr);
            while (br.readLine() != null) {
                String str = br.readLine();
                if (str != null) {
                    if (str.contains("|")) {
                        String[] parts = null;
                        parts = str.split("\\|", -1);
                        if (parts[0].isEmpty() || parts[11].isEmpty()) {
                            continue;
                        }
                        count++;
                        if (count == line) {
                            moveToErrorFolder(str, filePath);
                        }
                    }
                }
            }
        } catch (IOException e) {
        }

    }

    public static String sqlInsertToTable() {
        String sql = null;
        sql = "INSERT INTO " + VASTableNameItem.TABLE_NAME
                + "(" + VASTableNameItem.MSISDN + ", " + VASTableNameItem.TIME + ", " + VASTableNameItem.PROGRAM_ID + ", "
                + VASTableNameItem.CONTENT + ", " + VASTableNameItem.LAC + ", " + VASTableNameItem.CELL_ID + ", "
                + VASTableNameItem.SHORT_CODE + ", " + VASTableNameItem.TITLE + ", " + VASTableNameItem.MENU_INDEX + ", "
                + VASTableNameItem.LONGITUDE + ", " + VASTableNameItem.LATITUDE + ", " + VASTableNameItem.VAS_TRANS_ID + ")"
                + " VALUES(?, to_date(?, '" + Config.formatOfDate + "'),"
                + " ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

//              	System.out.println(sql.substring(148));

        return sql;
    }

    public static void moveFileToOk(String filePath) {
        //move file inserted to OkFolder
        String index;
        String nameFile = null;
        File newFile = new File(filePath);
        File desFile = new File(Config.okDir + File.separator + newFile.getName());


        int k = newFile.getName().lastIndexOf(".");
        Calendar cal = Calendar.getInstance();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd_HHmmss");
        index = sdf.format(cal.getTime());
        nameFile = newFile.getName().substring(0, k)
                + "_" + index + "." + newFile.getName().substring(k + 1);

        if (!newFile.renameTo(new File(Config.okDir + File.separator + nameFile))) {

            k = desFile.getName().lastIndexOf(".");
            cal = Calendar.getInstance();
            sdf = new SimpleDateFormat("yyyyMMdd_HHmmss");
            index = sdf.format(cal.getTime());
            nameFile = desFile.getName().substring(0, k)
                    + "_" + index + "." + desFile.getName().substring(k + 1);
            System.out.println("Move to " + Config.okDir + File.separator + nameFile);
            newFile.renameTo(new File(Config.okDir + File.separator + nameFile));
        }

    }

    private static void moveToErrorFolder(String str, String tmpPath) {
        //copy file insert error to error folder
        try {
            File newFile = new File(Config.sourceDir + File.separator + tmpPath + ".tmp");
            if (!newFile.exists()) {
                newFile.createNewFile();
                FileOutputStream fos = new FileOutputStream(newFile, true);
                PrintWriter print = new PrintWriter(fos);
                print.println(str);
                print.close();
            } else {
                System.gc();
                newFile.delete();
                FileOutputStream fos = new FileOutputStream(newFile, true);
                PrintWriter print = new PrintWriter(fos);
                print.println(str);
                print.close();
            }

        } catch (Exception e) {
//        	System.out.println("3");
        }
    }

    private static boolean isValidDate(String inDate) {

        try {
            DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
            df.setLenient(false);
            df.parse(inDate);
        } catch (ParseException e) {
            return false;
        }
        try {
            DateFormat df = new SimpleDateFormat("d/MM/yyyy");
            df.setLenient(false);
            df.parse(inDate);
        } catch (ParseException e) {
            return false;
        }
        try {
            DateFormat df = new SimpleDateFormat("dd/M/yyyy");
            df.setLenient(false);
            df.parse(inDate);
        } catch (ParseException e) {
            return false;
        }
        try {
            DateFormat df = new SimpleDateFormat("d/M/yyyy");
            df.setLenient(false);
            df.parse(inDate);
        } catch (ParseException e) {
            return false;
        }
        return true;

    }

    private static boolean isNumberic(String number) {
        if (number.length() == 0) {
            return true;
        }
        try {
            NumberFormat.getInstance().parse(number);
//			Double.parseDouble(number);
        } catch (Exception e) {
            return false;
        }
        return true;
    }

    private static boolean isInteger(String number) {
        try {
            Integer.parseInt(number);
        } catch (Exception e) {
            return false;
        }
        return true;
    }
}
